﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace mamnontuoitho7.AppCode
{
    public class KidHeal
    {

        public ConnectGetData _connectData;
        public KidHeal()
        {
            _connectData = new ConnectGetData();
        }


        /// <summary>
        /// Insert Health Information of Kid into database
        /// </summary>
        /// <param name="teacherID"></param>
        /// <param name="kidID"></param>
        /// <param name="kidWeight"></param>
        /// <param name="weightNotice"></param>
        /// <param name="kidHeight"></param>
        /// <param name="heightNotice"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public bool KidHealCreate(String teacherID, String kidID, String kidWeight, String weightNotice, String kidHeight, String heightNotice, String result)
        {
            SqlConnection con = _connectData.GetConnect();

            try
            {
                string sql = "insert KidHealInformation(KidID, MemberID, KidWeight, WeightNotice, KidHeight, HeightNotice, Result) values"+
                                                    "(@kidID, @teacherID, @kidWeight, @weightNotice, @kidHeight, @heightNotice, @result)";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@kidID", kidID);
                cmd.Parameters.AddWithValue("@teacherID", teacherID);
                cmd.Parameters.AddWithValue("@kidWeight", kidWeight);
                cmd.Parameters.AddWithValue("@weightNotice", weightNotice);
                cmd.Parameters.AddWithValue("@kidHeight", kidHeight);
                cmd.Parameters.AddWithValue("@heightNotice", heightNotice);
                cmd.Parameters.AddWithValue("@result", result);

                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
            return true;
        }


        /// <summary>
        /// Update Kid Health Information into database
        /// </summary>
        /// <param name="teacherID"></param>
        /// <param name="kidInformationID"></param>
        /// <param name="kidWeight"></param>
        /// <param name="weightNotice"></param>
        /// <param name="kidHeight"></param>
        /// <param name="heightNotice"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        internal bool KidHealUpdate(String teacherID, String kidInformationID, String kidWeight, String weightNotice, String kidHeight, 
            String heightNotice, String result)
        {
            string sql = "update KidHealInformation set MemberID=@teacherID, KidWeight=@kidWeight, WeightNotice=@weightNotice, KidHeight=@kidHeight, " +
                "HeightNotice=@heightNotice, Result=@result where KidInformationID = @kidInformationID";
            try
            {
                SqlConnection con = _connectData.GetConnect();
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@kidInformationID", kidInformationID);
                cmd.Parameters.AddWithValue("@teacherID", teacherID);
                cmd.Parameters.AddWithValue("@kidWeight", kidWeight);
                cmd.Parameters.AddWithValue("@weightNotice", weightNotice);
                cmd.Parameters.AddWithValue("@kidHeight", kidHeight);
                cmd.Parameters.AddWithValue("@heightNotice", heightNotice);
                cmd.Parameters.AddWithValue("@result", result);

                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
            return true;
        }


        /// <summary>
        /// Get Heal Information of a Kid
        /// </summary>
        /// <param name="kidID"></param>
        /// <returns></returns>
        internal DataTable GetKidHealInformation(String kidID)
        {
            SqlConnection con = _connectData.GetConnect();
            string sqlUp = "select * from KidHealInfoView where KidID = @kidID order by KidInformationID DESC";
            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@kidID", kidID);

            return _connectData.GetDataTable(cmd);
        }




        internal DataRow GetOneRecordKidHealthInformation(String kidInformationID)
        {
            SqlConnection con = _connectData.GetConnect();
            string sqlUp = "select * from KidHealInfoView where KidInformationID = @kidInformationID";
            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@kidInformationID", kidInformationID);

            return _connectData.GetDataTable(cmd).Rows[0];
        }


        /// <summary>
        /// Delete Heal Information of a kid
        /// </summary>
        /// <param name="p"></param>
        internal void DeleteKidHealInfo(String kidInformationID)
        {
            SqlConnection con = _connectData.GetConnect();

            string sqlUp = "Delete KidHealInformation " +
                                            " where KidInformationID = @kidInformationID ";

            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@kidInformationID", kidInformationID);

            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }

        /// <summary>
        /// get total number of wall of one kid
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalHealOfOneKid(String kidID)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();

            string sql = "SELECT COUNT(*) FROM KidHealInformation WHERE  KidID =" + kidID;
            SqlCommand cmd = new SqlCommand(sql, con);

            object returnValue;

            returnValue = cmd.ExecuteScalar();
            con.Close();
            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }

        /// <summary>
        /// get a number of record of a page 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="step"></param>
        /// <returns>datatable</returns>
        public DataTable GetHealPaged(int pageIndex, int step, String kidID)
        {
            //int startRowIndex = pageIndex * step + 1;
            //int endRowIndex = startRowIndex + step -1;
            int startRowIndex = (pageIndex - 1) * step + 1;
            int endRowIndex = startRowIndex + step - 1;
            string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY KidInformationID DESC) AS number, * FROM KidHealInformation where KidID = " + kidID + " ) AS TempTable WHERE number BETWEEN @startRowIndex AND @endRowIndex";

            SqlConnection con = _connectData.GetConnect();
            
            SqlCommand cmd = new SqlCommand(sql, con);

            cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
            cmd.Parameters.AddWithValue("@endRowIndex", endRowIndex);
            DataTable dt = _connectData.GetDataTable(cmd);
       
            return dt;
        }



    }
}